package cn.softwin.dao.impl;

import cn.softwin.dao.CustumerDao;
import cn.softwin.pojo.CstCustomer;
import cn.softwin.pojo.CstCustomerDetail;
import cn.softwin.utils.JDBCpoolUtils;
import jdk.nashorn.internal.scripts.JD;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CustumerDaoImpl implements CustumerDao {

    // 操作数据库的dao
    private Connection con = null;
    private Connection con1 = null;
    private PreparedStatement pstat = null;
    private PreparedStatement pstat1 = null;
    private Long maxId = 0l;
    private ResultSet rs =null;
    
    @Override
    public Boolean addCustumer(CstCustomer cstCustomer) {
        try {
            con = JDBCpoolUtils.getConnection();
            String sql = "insert into cst_customer (cust_name,cust_source,cust_industry,cust_level,cust_phone,cust_mobile) values(?,?,?,?,?,?)";
            pstat = con.prepareStatement(sql);
            pstat.setString(1, cstCustomer.getCust_name());
            pstat.setString(2, cstCustomer.getCust_source());
            pstat.setString(3, cstCustomer.getCust_industry());
            pstat.setString(4, cstCustomer.getCust_level());
            pstat.setString(5, cstCustomer.getCust_phone());
            pstat.setString(6, cstCustomer.getCust_mobile());
            int count = pstat.executeUpdate();
            if (count>0) {
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCpoolUtils.close(pstat, con);
        }
        return false;
    }

    @Override
    public Boolean addCustumerDetails(CstCustomerDetail cstCustomerDetail) {
        try {
            Connection conn = JDBCpoolUtils.getConnection();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT MAX(cust_id) FROM cst_customer ");
            if (rs.next()){
                maxId = rs.getLong(1);
            }
            conn.close();
            con1 = JDBCpoolUtils.getConnection();
            String sql = "insert into cst_customer_detail (cust_region,cust_zip,cust_address,cust_fax,cust_website,cust_licence,cust_corporation,cust_capital,cust_bank,cust_pic,cust_memo,cust_id) values(?,?,?,?,?,?,?,?,?,?,?,?)";
            pstat1 = con1.prepareStatement(sql);
            pstat1.setString(1, cstCustomerDetail.getCust_region());
            pstat1.setString(2, cstCustomerDetail.getCust_zip());
            pstat1.setString(3, cstCustomerDetail.getCust_address());
            pstat1.setString(4, cstCustomerDetail.getCust_fax());
            pstat1.setString(5, cstCustomerDetail.getCust_website());
            pstat1.setString(6, cstCustomerDetail.getCust_licence());
            pstat1.setString(7, cstCustomerDetail.getCust_corporation());
            pstat1.setLong(8, cstCustomerDetail.getCust_capital());
            pstat1.setString(9, cstCustomerDetail.getCust_bank());
            pstat1.setString(10, cstCustomerDetail.getCust_pic());
            pstat1.setString(11, cstCustomerDetail.getCust_memo());
            pstat1.setLong(12,maxId);
            int i = pstat1.executeUpdate();
            if (i > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCpoolUtils.close(pstat1, con1);
        }
        return false;
    }

    /**
     * Description: 根据当前页和每页显示的行数进行分页查询，返回封装好的对象结果集
     * Author: zhangxiangkai
     * Params: [start, rows]
     * ReturnType: java.util.List<cn.softwin.pojo.CstCustomer>
     * Time: 2021/2/18-11:59
     */
    @Override
    public List<CstCustomer> selectByPage(int start, int rows) {
        List<CstCustomer> list = null;
        ResultSet rs = null;
        try {
            con = JDBCpoolUtils.getConnection();
            String sql = "SELECT * FROM cst_customer limit ?,?";
            PreparedStatement pst = con.prepareStatement(sql);
            pst.setInt(1,start);
            pst.setInt(2,rows);
            rs = pst.executeQuery();
            list = new ArrayList<>();
            while (rs.next()) {
                Long id = rs.getLong("cust_id");
                String name = rs.getString("cust_name");
                String source = rs.getString("cust_source");
                String industry = rs.getString("cust_industry");
                String level = rs.getString("cust_level");
                String phone = rs.getString("cust_phone");
                String mobile = rs.getString("cust_mobile");
                CstCustomer cstCustomer = new CstCustomer();
                cstCustomer.setCust_id(id);
                cstCustomer.setCust_industry(industry);
                cstCustomer.setCust_level(level);
                cstCustomer.setCust_mobile(mobile);
                cstCustomer.setCust_phone(phone);
                cstCustomer.setCust_name(name);
                cstCustomer.setCust_source(source);
                list.add(cstCustomer);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            JDBCpoolUtils.close(rs,null,con);
        }
        return list;
    }

    /**
     * Description: 查询当前表所有数据的行数
     * Author: zhangxiangkai
     * Params: []
     * ReturnType: int
     * Time: 2021/2/18-11:58
     */
    @Override
    public int selectTotalCount() {
        Statement stat = null;
        ResultSet res = null;
        int totalCount =0;
        try {
            con = JDBCpoolUtils.getConnection();
            String sql = "SELECT count(*) FROM cst_customer";
            stat = con.createStatement();
            res = stat.executeQuery(sql);
            while (res.next()) {
                totalCount = res.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCpoolUtils.close(res, stat, con);
        }
        return totalCount;
    }

    /**
     * Description: 根据id删除对应的客户信息
     * Author: zhangxiangkai
     * Params: [custId]
     * ReturnType: boolean
     * Time: 2021/2/18-17:27
     */
    @Override
    public boolean deleteCustomerById(Long custId) {
        int totalCount =0;
        try {
            con = JDBCpoolUtils.getConnection();
            String sql = "DELETE FROM cst_customer WHERE cust_id=?";
            pstat = con.prepareStatement(sql);
            pstat.setLong(1,custId);
            int count = pstat.executeUpdate();
            if (count>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCpoolUtils.close(pstat, con);
        }
        return false;
    }

    /**
     * Description: 根据id查询客户详细信息
     * Author: zhangxiangkai
     * Params: [custId]
     * ReturnType: cn.softwin.pojo.CstCustomerDetail
     * Time: 2021/2/18-20:37
     */
    @Override
    public CstCustomerDetail queryCustomerDetailsById(Long custId) {
        CstCustomerDetail cstCustomerDetail = new CstCustomerDetail();
        try {
            con = JDBCpoolUtils.getConnection();
            String sql = "SELECT * FROM cst_customer_detail WHERE cust_id=?";
            pstat = con.prepareStatement(sql);
            pstat.setLong(1,custId);
            rs = pstat.executeQuery();
            if (rs.next()){
                cstCustomerDetail = new CstCustomerDetail();
                String cust_region = rs.getString("cust_region");
                String cust_zip = rs.getString("cust_zip");
                String cust_address = rs.getString("cust_address");
                String cust_fax = rs.getString("cust_fax");
                String cust_website = rs.getString("cust_website");
                String cust_licence = rs.getString("cust_licence");
                String cust_corporation = rs.getString("cust_corporation");
                Long cust_capital = rs.getLong("cust_capital");
                String cust_bank = rs.getString("cust_bank");
                String cust_pic = rs.getString("cust_pic");
                String cust_memo = rs.getString("cust_memo");
                cstCustomerDetail.setCust_address(cust_address);
                cstCustomerDetail.setCust_bank(cust_bank);
                cstCustomerDetail.setCust_capital(cust_capital);
                cstCustomerDetail.setCust_fax(cust_fax);
                cstCustomerDetail.setCust_pic(cust_pic);
                cstCustomerDetail.setCust_zip(cust_zip);
                cstCustomerDetail.setCust_website(cust_website);
                cstCustomerDetail.setCust_corporation(cust_corporation);
                cstCustomerDetail.setCust_licence(cust_licence);
                cstCustomerDetail.setCust_memo(cust_memo);
                cstCustomerDetail.setCust_region(cust_region);
                cstCustomerDetail.setCust_id(custId);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return cstCustomerDetail;
    }

    /**
     * Description: 根据id查询对应的Customer数据
     * Author: zhangxiangkai
     * Params: [custId]
     * ReturnType: cn.softwin.pojo.CstCustomer
     * Time: 2021/2/18-20:37
     */
    @Override
    public CstCustomer queryCustomerById(Long custId) {
        CstCustomer cstCustomer = new CstCustomer();
        String sql = "SELECT * FROM cst_customer WHERE cust_id=?";
        try {
            con = JDBCpoolUtils.getConnection();
            pstat = con.prepareStatement(sql);
            pstat.setLong(1,custId);
            rs = pstat.executeQuery();
            if (rs.next()){
                String cust_name = rs.getString("cust_name");
                String source = rs.getString("cust_source");
                String cust_industry = rs.getString("cust_industry");
                String level = rs.getString("cust_level");
                String cust_phone = rs.getString("cust_phone");
                String mobile = rs.getString("cust_mobile");
                cstCustomer.setCust_id(custId);
                cstCustomer.setCust_source(source);
                cstCustomer.setCust_name(cust_name);
                cstCustomer.setCust_phone(cust_phone);
                cstCustomer.setCust_level(level);
                cstCustomer.setCust_mobile(mobile);
                cstCustomer.setCust_industry(cust_industry);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return cstCustomer;
    }

    /**
     * Description: 根据id对客户信息进行修改
     * Author: zhangxiangkai
     * Params: [custId]
     * ReturnType: java.lang.Boolean
     * Time: 2021/2/18-21:43
     */
    @Override
    public Boolean updateCustomerById(CstCustomer cstCustomer) {
        try {
            con = JDBCpoolUtils.getConnection();
            String sql = "UPDATE cst_customer SET cust_name=?,cust_source=?,cust_industry=?,cust_level=?,cust_phone=?,cust_mobile=? WHERE cust_id=?";
            pstat = con.prepareStatement(sql);
            pstat.setString(1,cstCustomer.getCust_name());
            pstat.setString(2,cstCustomer.getCust_source());
            pstat.setString(3,cstCustomer.getCust_industry());
            pstat.setString(4,cstCustomer.getCust_level());
            pstat.setString(5,cstCustomer.getCust_phone());
            pstat.setString(6,cstCustomer.getCust_mobile());
            pstat.setLong(7,cstCustomer.getCust_id());
            int count = pstat.executeUpdate();
            if (count>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    /**
     * Description: 根据id对客户详细信息进行修改
     * Author: zhangxiangkai
     * Params: [custId]
     * ReturnType: java.lang.Boolean
     * Time: 2021/2/18-21:43
     */
    @Override
    public Boolean updateCustomerDetailById(CstCustomerDetail cstCustomerDetail) {
        try {
            con = JDBCpoolUtils.getConnection();
            String sql = "UPDATE cst_customer_detail SET cust_region=?,cust_zip=?,cust_address=?,cust_fax=?,cust_website=?,cust_licence=?,cust_capital=?,cust_bank=?,cust_pic=?,cust_memo=? WHERE cust_id=?";
            pstat = con.prepareStatement(sql);
            pstat.setString(1,cstCustomerDetail.getCust_region());
            pstat.setString(2,cstCustomerDetail.getCust_zip());
            pstat.setString(3,cstCustomerDetail.getCust_address());
            pstat.setString(4,cstCustomerDetail.getCust_fax());
            pstat.setString(5,cstCustomerDetail.getCust_website());
            pstat.setString(6,cstCustomerDetail.getCust_licence());
            pstat.setLong(7,cstCustomerDetail.getCust_capital());
            pstat.setString(8,cstCustomerDetail.getCust_bank());
            pstat.setString(9,cstCustomerDetail.getCust_pic());
            pstat.setString(10,cstCustomerDetail.getCust_memo());
            pstat.setLong(11,cstCustomerDetail.getCust_id());
            int count = pstat.executeUpdate();
            if (count>0){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }
}
